import pandas as pd
#显示完整的列
pd.set_option('display.max_columns', None)
#显示完整的行
pd.set_option('display.max_rows', None)

def get_title(df):
    title = []
    time = []
    for index, row in df.iterrows():
        # print(row, type(row))  # 或者 print(row.tolist()) 来将每行转换为列表并打印
        Keys = row.keys()
        for k in Keys :
            org = k.split("\n")
            if len(org) == 1 and org[0] not in title:
                title.append(org[0])
            if len(org) > 1:
                tim = org[1].split(']')[-1].split('年')[0]
                unit = org[-1].split(']')[-1]
                tit = org[0]+'/'+ unit
                if tit not in title:
                    title.append(tit)
                time.append(tim)
    title.insert(2, "时间")
    return title, time, Keys



def insert_data(df, title, time, Keys):
    data_list_total = []
    for index, row in df.iterrows():
        kk = ''
        for k  in row.keys():
            if "全部明细" in k:
                kk = k
                break

        data_list = []
        for indx, value in row.items():
            data = {}
            time_list = []
            data["证券代码"] = row["证券代码"]
            data["证券简称"] = row["证券简称"]
            # data["省份"] = row["省份"]
            # data['行业级别'] = row[kk]
            # data['成立日期'] = row["成立日期"]
            # data['上市日期'] = row["上市日期"]
            lt = indx.replace(" ", "").split("\n")
            # print(f"indx:{indx},value:{value}")
            if len(lt)>1:
                time = lt[1].split(']')[-1].split('年')[0]
                if "Z值" in lt[0]:
                    unit = ''
                else:
                    unit = lt[-1].split(']')[-1]
                Asset_liability_ratio = lt[0]+"/"+unit
                if "负债率" in lt[0] or "Z值" in lt[0]:
                    print("lt[0]:",lt[0], Asset_liability_ratio,time, unit, value)
                if time not in time_list:
                    time_list.append(time)
                    data["时间"] = time
                data[Asset_liability_ratio]=value
            # print(data)
            # if len(data)>4:
            if len(data)>2:
                data_list.append(data)
        data_list_total.extend(data_list)
    # print(data_list_total, len(data_list_total))
    return data_list_total

def merged_data(data_list):
    # 创建一个空字典来累积合并后的结果
    merged_data = {}
    # 遍历列表中的每个字典
    for d in data_list[:-3]:
        print(d, type(d))
        # if "时间" not in d.keys():
        #     continue
        if "时间" in d.keys() and "上市首日" == d['时间']:
            continue
        # 提取合并的键
        key = (d['证券代码'], d['证券简称'], d['时间'])
        # key = (d['证券代码'], d['证券简称'], d['时间'], d['行业级别'], d["省份"], d['成立日期'])

        # 如果这个键已经存在于累积字典中，则合并字典
        if key in merged_data:
            merged_data[key].update(d)
        else:
            # 否则，创建一个新字典
            merged_data[key] = d.copy()

            # 将累积字典的values转换为列表
    final_data = list(merged_data.values())

    # 输出结果
    # print(final_data, len(final_data))
    return final_data


def save_excel(data_list, output_file_path):
    # 创建一个DataFrame
    df = pd.DataFrame(data_list)

    # 写入Excel文件，注意这里'index=False'是为了不在Excel中写入行索引
    df.to_excel(output_file_path, index=False, engine='openpyxl')

    print("Excel文件已保存。")

import numpy as np
def normalize_excel_columns(file_path, output_file_path,  sheet_name, columns_to_normalize):
    # 读取Excel文件
    df = pd.read_excel(file_path, sheet_name=sheet_name)

    # 遍历要处理的列
    for col in columns_to_normalize:
        if col in df.columns:
            # 计算该列的均值和标准差
            mean_val = df[col].mean()
            std_dev = df[col].std()

            # 将"--"替换为NaN，这样它们就不会影响min和max的计算
            df[col] = df[col].replace("--", pd.NA)

            # 获取列的最小值和最大值
            min_val = df[col].min()
            max_val = df[col].max()

            # # 检查最小值是否等于最大值（或NaN），避免除以零
            # if pd.isna(min_val) or pd.isna(max_val) or min_val == max_val:
            #     # 如果所有有效值都相同或存在NaN，可以将整列设置为0.1（或任意常数）
            #     df[col] = 0.1
            # # elif :
            # else:


            df[col] = np.where(df[col] != 0, (df[col] - mean_val) / std_dev, df[col])
                # # # 应用归一化公式
                # # df[col] = 0.2 + 0.8 * (df[col] - min_val) / (max_val - min_val)
                # # # 应用[-1,1]的标准化
                # # df[col] = (df[col] - min_val) * 2 / (max_val - min_val) - 1
                # df[col] = (df[col] - mean_val) / std_dev
        else:
            print(f"列名 {col} 不存在于Excel文件中。")

            # 将处理后的DataFrame写回Excel文件（这里以新文件为例，你可以修改文件名来覆盖原文件）
    # output_file_path = 'normalized_' + file_path
    df.to_excel(output_file_path, index=False, sheet_name=sheet_name)
    print(f"处理后的数据已保存到 {output_file_path}")


def copy_matching_rows(file1_path, file2_path, f1_sheet_name, f2_sheet_name='Sheet1', match_columns=[0, 1], output_file='updated_注册制_excel.xlsx'):
    """
    从第二个Excel文件中复制与第一个Excel文件前两列匹配的整行到第一个Excel文件的末尾。

    参数:
    file1_path (str): 第一个Excel文件的路径。
    file2_path (str): 第二个Excel文件的路径。
    match_columns (list of int): 用于匹配的列索引列表，默认为[0, 1]（即前两列）。
    output_file (str): 输出文件的名称，默认为'updated_first_excel.xlsx'。

    返回:
    None: 直接将结果保存到输出文件。
    """
    # 读取Excel文件
    df1 = pd.read_excel(file1_path, sheet_name=f1_sheet_name)
    df2 = pd.read_excel(file2_path, sheet_name=f2_sheet_name)

    # 创建一个空的DataFrame来存储结果
    result_df = df1.copy()
    # 创建一个空的DataFrame列表来存储要添加到结果中的行
    rows_to_add = []

    i = 0
    # 遍历df2的每一行，检查第一列的值是否在df1的第一列中存在
    d1 = df1.iloc[:, 0].values
    print(len(d1), d1)
    for index, row in df2.iterrows():
        # print(row.iloc[0])
        if row.iloc[0] in d1:  # 假设第一列是我们想要比较的列
            i+=1
            print(row)
            rows_to_add.append(row)
    if rows_to_add:
        result_df = pd.DataFrame(rows_to_add)
            #
    #         # 注意：这里我们简单地在末尾追加了所有匹配的行，如果需要更复杂的合并逻辑，请考虑使用merge或concat
    print(i)
    # # 保存结果到新的Excel文件
    result_df.to_excel(output_file, index=False)


def merge_excel_based_on_codes_and_dates(file1_path, file2_path, output_file_path):
    """
    根据证券代码和时间（或统计截止日期）合并两个Excel文件。

    参数:
    file1_path (str): 第一个Excel文件的路径。
    file2_path (str): 第二个Excel文件的路径。
    output_file_path (str): 合并后Excel文件的保存路径。

    假设：
    - 第一个Excel文件的前两列为“证券代码”和“时间”。
    - 第二个Excel文件包含“证券代码”、“统计截止日期”和“主营收入增长率”。
    - “时间”列和“统计截止日期”列可以直接比较，或者已经通过适当的预处理变得可比较。

    返回:
    None（但会保存合并后的Excel文件到指定路径）
    """
    # 读取Excel文件
    df1 = pd.read_excel(file1_path, sheet_name='Sheet1')
    df2 = pd.read_excel(file2_path, sheet_name='sheet1')

    # 确保列名匹配（如果需要的话）
    # 这里假设列名已经正确，不需要额外处理

    # 创建一个新列来存储主营收入增长率，初始化为NaN
    # df1['主营收入增长率'] = pd.NA

    # 合并两个DataFrame
    merged_df = pd.merge(df1, df2[['证券代码', '统计截止日期', '主营收入增长率']],
                         left_on=['证券代码', '时间'], right_on=['证券代码', '统计截止日期'],
                         how='left')
    # 打印合并后的DataFrame的列名以进行检查
    print(merged_df.columns)
    # 选择我们想要的列（即df1的原始列加上合并后的'主营收入增长率'列）
    # 注意：这里假设没有额外的索引列需要排除
    print(df1.columns.tolist())
    result_df = merged_df[df1.columns.tolist() + ['主营收入增长率']]

    # 保存结果到新的Excel文件
    result_df.to_excel(output_file_path, index=False)


def merge_excel_dates(file_path1, file_path2, output_path):
    # 读取Excel文件
    df1 = pd.read_excel(file_path1, sheet_name='万得')  # 第一个Excel
    df2 = pd.read_excel(file_path2, sheet_name='Sheet2')  # 第二个Excel

    # 假设列名分别是 ['证券简码', '日期', ...] 和 ['证券简码', '时间', ...]
    # 创建一个新的列来存储匹配的日期
    df2['匹配的日期'] = None

    # 遍历df1中的每一行，查找df2中证券简码和时间都匹配的行，并更新df2的'匹配时间'列
    for index, row in df1.iterrows():
        # 使用merge来查找匹配的行，但这里只关心是否存在匹配，因此用any()简化逻辑
        mask = (df2['证券代码'] == row['证券代码']) & (df2['时间'] == row['时间'])
        if mask.any():
            # 如果存在匹配项，则更新df2中所有匹配行的'匹配时间'列
            df2.loc[mask, '匹配时间'] = row['时间']

            # 保存修改后的df2到新的Excel文件
    df2.to_excel(output_path, index=False)


import os

def normal_func():
    file_path = "D:\北工大\B503\合作论文\注册制和金融效率\数据\A股\原始数据"
    # need_normaliz_file = ['核准制上证A股-数据.xlsx', '核准制深证A股-数据.xlsx', '注册制上证A股-数据.xlsx',
    #                       '注册制深证A股-数据.xlsx']
    need_normaliz_file = ['中介&控制.xlsx']
    columns_to_normalize = ['股东权益合计/百万元', '净利润/百万元', '资产负债率/%', '财务费用/百万元', '总资产/百万元',
                            '总资产报酬率ROA/%', '总负债/百万元', '无形资产/百万元', 'Z值/', "现金流状况", "财务杠杆率",
                            '主营业务成本/百万元', '营业收入(N年,增长率)/%', '前五大股东持股比例合计/%', 'Z指数', 'SA指数','融资成本',
                            '总资产周转率/次', '净资产收益率ROE/%', '营业总成本/百万元', '主营收入/百万元',
                            '主营收入增长率', '经营活动产生的现金流量净额/百万元']  # 修改为你的列名
    financing_efficiency_normalize = [
        '总资产周转率/次'    '净资产收益率ROE/%'    '主营业务成本/百万元'    '资产负债率/%'    '财务费用/百万元']
    sheet_name = '中介'

    for file in need_normaliz_file:
        open_file_path = os.path.join(file_path, file)
        fn = file.split(".")
        out_file_name = fn[0] + '_normalized-0_1.' + fn[-1]
        output_file_path = os.path.join(file_path, out_file_name)
        print(f'{open_file_path}, {out_file_name}, {sheet_name}, ')
        normalize_excel_columns(open_file_path, output_file_path, sheet_name, columns_to_normalize)

# 填充空值的函数
def fill_missing_values(group):
    # 获取所有年份的列
    columns = group.columns[7:]
    # print(columns)
    print('len(group):', len(group))

    for i in range(len(group)):
    # 对每一列应用填充逻辑
        for column in columns:
            # 某家公司的某一列值是空值
            if pd.isnull(group.iloc[i][column]):
                print(f'{group.iloc[i][column]}, {i}, {column}')
                # 向前检查
                if i > 0 and not pd.isnull(group.iloc[i - 1][column]):

                    group.at[i, column] = group.iloc[i - 1][column]
                    print(group.at[i, column])

                elif i > 1 and not pd.isnull(group.iloc[i - 2][column]):
                    group.at[i, column] = group.iloc[i - 2][column]
                    print(group.at[i, column])
                elif i > 2 and not pd.isnull(group.iloc[i - 3][column]):
                    group.at[i, column] = group.iloc[i - 3][column]
                    print(group.at[i, column])

                # # 向后检查
                # elif i < len(group) - 1 and not pd.isnull(group.iloc[i + 1][column]):
                #     group.at[i, column] = group.iloc[i + 1][column]
                # elif i < len(group) - 2 and not pd.isnull(group.iloc[i + 2][column]):
                #     group.at[i, column] = group.iloc[i + 2][column]
                # elif i < len(group) - 3 and not pd.isnull(group.iloc[i + 3][column]):
                #     group.at[i, column] = group.iloc[i + 3][column]

    print('return len group', len(group))
    return group


def fill_missing_values_inplace(df, start_col=7):
    columns = df.columns[start_col:]
    for i in range(1, len(df)):
        for column in columns:
            if pd.isnull(df.at[i, column]):
                print(column)
                if i < len(df) - 1 and not pd.isnull(df.iloc[i + 1][column]):
                    df.at[i, column] = df.at[i + 1, column]
                elif i < len(df) - 2 and not pd.isnull(df.iloc[i + 2][column]):
                    df.at[i, column] = df.iloc[i + 2][column]
                elif i < len(df) - 3 and not pd.isnull(df.iloc[i + 3][column]):
                    df.at[i, column] = df.iloc[i + 3][column]
def fill_excel():
    need_process_file = ['前五大股东持股比例-格式转换.xlsx']
    # need_process_file = ['核准制上证A股-数据.xlsx', '核准制深证A股-数据.xlsx', '注册制上证A股-数据.xlsx',
    #                       '注册制深证A股-数据.xlsx']
    path = 'D:\北工大\B503\合作论文\注册制和金融效率\数据\A股'
    for file in need_process_file:
        print(file)
        fn = file.split(".")
        open_file_path = 'D:\北工大\B503\合作论文\注册制和金融效率\数据\A股\前五大股东持股比例-格式转换_normalized-0_1_filled_data_1.xlsx'
        # open_file_path = os.path.join(path, fn[0]+'_normalized-0_1.'+fn[-1])
        # out_file_name = fn[0] + '_normalized-0_1_filled_data.' + fn[-1]
        out_file_name = 'D:\北工大\B503\合作论文\注册制和金融效率\数据\A股\前五大股东持股比例-格式转换_normalized-0_1_filled_data_2.xlsx'
        # file_path = path + "\注册制上证A股-数据_normalized-0_1.xlsx"
        df = pd.read_excel(open_file_path,sheet_name='Sheet1')

        fill_missing_values_inplace(df, start_col=3)

        # 保存到新的 Excel 文件
        df.to_excel(os.path.join(path, out_file_name), index=False)

def convert_data():
    # 指定Excel文件路径
    file_path = 'D:\北工大\B503\合作论文\注册制和金融效率\数据\A股\原始数据\全部A股-总数据.xlsx'
    output_file_path = 'D:\北工大\B503\合作论文\注册制和金融效率\数据\A股\原始数据\全部A股-总数据-格式转换.xlsx'
    # 使用pandas的read_excel函数读取Excel文件，sheet_name参数指定要读取的Sheet名或索引
    # 如果不指定sheet_name，则默认读取第一个Sheet
    df = pd.read_excel(file_path, sheet_name='中介')

    title, time, Keys = get_title(df)
    data_list = insert_data(df, title, time, Keys)
    save_excel(merged_data(data_list), output_file_path)

if __name__ == '__main__':
    # 1 格式转换
    # convert_data()

    # # #2  证券代码分列   3、 添加增长率
    # file1_path = 'D:\北工大\B503\合作论文\注册制和金融效率\数据\融资效率\核准制创业板_output.xlsx'
    # file2_path = 'D:\北工大\B503\合作论文\注册制和金融效率\数据\融资效率\CSR_Finidx.xlsx'
    # output_file_path = 'D:\北工大\B503\合作论文\注册制和金融效率\数据\融资效率\核准制创业板_CSR.xlsx'

    # merge_excel_based_on_codes_and_dates(file1_path, file2_path, output_file_path)


    ##### 4 归一化
    normal_func()
    # 数据填充
    # fill_excel()
    # # 数据拆分 file2_path 被拆分的excel
    # file1_path = 'D:\北工大\B503\合作论文\注册制和金融效率\数据\A股\注册制深证A股-代码.xlsx'
    # file2_path = 'D:\北工大\B503\合作论文\注册制和金融效率\数据\A股\全部A股-总数据-格式转换.xlsx'
    # output_file = 'D:\北工大\B503\合作论文\注册制和金融效率\数据\A股\注册制深证A股-数据.xlsx'
    # f1_sheet_name = '注册制深证A股'
    # copy_matching_rows(file1_path, file2_path,f1_sheet_name, match_columns=[0], output_file=output_file)

    # # 填充上市日期
    # file_path1 = 'D:\北工大\B503\合作论文\注册制和金融效率\数据\上市时间\注册制企业上市时间.xlsx'
    # file_path2 = 'D:\北工大\B503\合作论文\注册制和金融效率\数据\控制变量\psm控制变量\psm_data.xlsx'
    # output_path = 'D:\北工大\B503\合作论文\注册制和金融效率\数据\融资效率\psm_data_ssrq.xlsx'
    # merge_excel_dates(file_path1, file_path2, output_path)
